package com.example.demo.mapper;

import com.example.demo.entity.TeacherCourse;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Optional;

@Mapper
public interface TeacherCourseMapper {

    String COLUMNS = "id, teacher_id, course_id, created_at";

    // 1. 根据 ID 查找单个关联记录
    @Select("SELECT " + COLUMNS + " FROM teacher_courses WHERE id = #{id}")
    Optional<TeacherCourse> findById(@Param("id") Integer id);

    // 2. 根据教师工号和课程 ID 查找关联记录 (利用 uk_teacher_course 唯一键)
    @Select("SELECT " + COLUMNS + " FROM teacher_courses WHERE teacher_id = #{teacherId} AND course_id = #{courseId}")
    Optional<TeacherCourse> findByTeacherIdAndCourseId(@Param("teacherId") String teacherId, @Param("courseId") String courseId);

    // 3. 查找某个教师教授的所有课程关联记录
    @Select("SELECT " + COLUMNS + " FROM teacher_courses WHERE teacher_id = #{teacherId}")
    List<TeacherCourse> findByTeacherId(@Param("teacherId") String teacherId);

    // 4. 查找某个课程对应的所有教师关联记录
    @Select("SELECT " + COLUMNS + " FROM teacher_courses WHERE course_id = #{courseId}")
    List<TeacherCourse> findByCourseId(@Param("courseId") String courseId);

    // 5. 插入新的关联记录 (建立关联)
    @Insert("INSERT INTO teacher_courses (teacher_id, course_id) VALUES (#{teacherId}, #{courseId})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(TeacherCourse association);

    // 6. 根据教师工号和课程 ID 删除关联记录 (解除关联)
    @Delete("DELETE FROM teacher_courses WHERE teacher_id = #{teacherId} AND course_id = #{courseId}")
    int deleteByTeacherIdAndCourseId(@Param("teacherId") String teacherId, @Param("courseId") String courseId);
}